﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Data.Common;
using System.Xml.Schema;

namespace DotNetMigrations.Commands
{
	[ConsoleCommand("bulkload")]
	public class BulkLoadCmd : ConsoleCommand
	{
		public BulkLoadCmd(string[] args) : base(args) { }

		private string _sourceFile;

		public override bool ValidateArgs()
		{
			if (ArgCount < 1)
			{
				Output.Warn("Connection string not found");
				return false;
			}

			DAL.ConnectionString = ArgFetcher.GetConnectionString(Args);

			if (ArgCount >= 2)
			{
				_sourceFile = Args[1];
			}
			if (String.IsNullOrEmpty(_sourceFile) ||
				!File.Exists(_sourceFile))
			{
				Output.Warn("Source file not specified or does not exist");
				return false;
			}

			return true;
		}

		public override bool Run()
		{
			long currentVersion = SchemaInfoTable.GetSchemaVersion();
			Output.Write("Database at version " + currentVersion);

			List<DbCommand> sqlCommands = new List<DbCommand>();
			bool executeCommands = true;

			//	Read the bulk load xml file and build a list of commands
			//	that we can execute against the database.
			FileStream stream = new FileStream(_sourceFile, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
			XmlReaderSettings readerSettings = new XmlReaderSettings();
			readerSettings.Schemas = GetSchemaSet();
			readerSettings.ValidationType = ValidationType.Schema;
			try
			{
				using (XmlReader reader = XmlReader.Create(stream, readerSettings))
				{
					string tableName = null;
					bool identityInsert = false;

					while (reader.Read())
					{
						if (reader.NodeType == XmlNodeType.Element && reader.Name == "bulkload")
						{
							long requiredVersion;
							if (long.TryParse(reader.GetAttribute("schema"), out requiredVersion)
								&& requiredVersion != currentVersion)
							{
								Output.Warn("Source file is not compatible with the database version");
								return false;
							}
						}

						if (reader.NodeType == XmlNodeType.Element && reader.Name == "table")
						{
							//	start next table
							tableName = reader.GetAttribute("name");

							bool truncate;
							if (bool.TryParse(reader.GetAttribute("truncate"), out truncate)
								&& truncate == true)
							{
								DbCommand cmd = DAL.GetCommand();
								if (DAL.GetProvider() == "System.Data.SqlClient")
									cmd.CommandText = "TRUNCATE TABLE [" + tableName + "]";
								else
									cmd.CommandText = "DELETE FROM [" + tableName + "]";
								sqlCommands.Add(cmd);
							}

							string deleteWhere = reader.GetAttribute("deleteWhere");
							if (!string.IsNullOrEmpty(deleteWhere))
							{
								DbCommand cmd = DAL.GetCommand();
								cmd.CommandText = "DELETE FROM [" + tableName + "] WHERE " + deleteWhere;
								sqlCommands.Add(cmd);
							}

							if (bool.TryParse(reader.GetAttribute("identityInsert"), out identityInsert)
								&& identityInsert)
							{
								DbCommand cmd = DAL.GetCommand();
								cmd.CommandText = "SET IDENTITY_INSERT [" + tableName + "] ON";
								sqlCommands.Add(cmd);
							}
						}

						if (reader.NodeType == XmlNodeType.EndElement && reader.Name == "table")
						{
							//	turn IDENTITY_INSERT OFF
							if (identityInsert)
							{
								DbCommand cmd = DAL.GetCommand();
								cmd.CommandText = "SET IDENTITY_INSERT [" + tableName + "] OFF";
								sqlCommands.Add(cmd);
							}

							//	reset values
							identityInsert = false;
							tableName = null;
						}

						if (reader.NodeType == XmlNodeType.Element && reader.Name == "row")
						{
							Dictionary<string, string> rowParams = new Dictionary<string, string>();
							while (reader.MoveToNextAttribute())
							{
								string columnName = reader.Name;
								string columnValue = reader.Value;

								rowParams.Add(columnName, columnValue);
							}

							#region build INSERT statement using row's parameters

							DbCommand cmd = DAL.GetCommand();
							StringBuilder sb = new StringBuilder();
							sb.Append("INSERT INTO [" + tableName + "] (");

							//	add column names to INSERT statement
							foreach (string columnName in rowParams.Keys)
							{
								sb.Append("[" + columnName + "]" + ",");
							}
							if (sb[sb.Length - 1] == ',')	//	trim trailing comma from column list
								sb.Remove(sb.Length - 1, 1);

							sb.Append(") VALUES (");

							//	add values to INSERT statement
							foreach (string columnName in rowParams.Keys)
							{
								sb.Append("@" + columnName + ",");
								cmd.Parameters.Add(DAL.GetParameter("@" + columnName, rowParams[columnName]));
							}
							if (sb[sb.Length - 1] == ',')	//	trim trailing comma from VALUES list
								sb.Remove(sb.Length - 1, 1);

							sb.Append(")");

							cmd.CommandText = sb.ToString();
							sqlCommands.Add(cmd);

							#endregion
						}

					}
				}
			}
			catch (XmlSchemaValidationException ex)
			{
				Output.Warn("Bulk load file is invalid");
				Output.Warn(ex.Message);
				executeCommands = false;
			}

			//	execute the SQL commands inside a transaction
			bool success = (executeCommands) ? DAL.ExecuteCommands(sqlCommands) : false;
			if (success)
			{
				Output.Write("Bulk load from " + Path.GetFileName(_sourceFile) + " complete");
			}
			else
			{
				Output.Warn("Bulk load from " + Path.GetFileName(_sourceFile) + " failed");
			}
			return success;
		}

		private static XmlSchemaSet GetSchemaSet()
		{
			XmlSchemaSet set = new XmlSchemaSet();
			string schemaText = Resource1.BulkLoadSchema;

			using (XmlReader reader = XmlReader.Create(new StringReader(schemaText)))
			{
				set.Add(null, reader);
			}

			return set;
		}
	}
}
